OpenEdge Development:<br />Programming Interfaces Reading XML data into temp-tables

Reading XML data into temp-tables

XML information read into a temp-table or temp-table buffer includes:

Schema for defining a ProDataSets’s temp-table fields and indexes.

Data for the temp-table rows.

This code example takes a temp-table with field definitions from the Customer table and reads an XML data file with three Customer records in it, The data file below shows the XML representation of the first record in the file:

<?xml version="1.0"?> <ttCust xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <ttCustRow> <CustNum>1</CustNum> <Country>USA</Country> <Name>Lift Tours</Name> <Address>276 North Drive</Address> <Address2/> <City>Burlington</City> <State>MA</State> <PostalCode>01730</PostalCode> <Contact>Gloria Shepley</Contact> <Phone>(617) 450-0086</Phone> <SalesRep>HXM</SalesRep> <CreditLimit>66700.0</CreditLimit> <Balance>903.64</Balance> <Terms>Net30</Terms> <Discount>35</Discount> <Comments>This customer is on credit hold.</Comments> <Fax/> <EmailAddress/> </ttCustRow> . . . </ttCust>

<?xml version="1.0"?> 
<ttCust xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <ttCustRow> 
    <CustNum>1</CustNum> 
    <Country>USA</Country> 
    <Name>Lift Tours</Name> 
    <Address>276 North Drive</Address> 
    <Address2/> 
    <City>Burlington</City> 
    <State>MA</State> 
    <PostalCode>01730</PostalCode> 
    <Contact>Gloria Shepley</Contact> 
    <Phone>(617) 450-0086</Phone> 
    <SalesRep>HXM</SalesRep> 
    <CreditLimit>66700.0</CreditLimit> 
    <Balance>903.64</Balance> 
    <Terms>Net30</Terms> 
    <Discount>35</Discount> 
    <Comments>This customer is on credit hold.</Comments> 
    <Fax/> 
    <EmailAddress/> 
  </ttCustRow> 
. 
. 
. 
</ttCust>

An include file sets up the static temp-table definition:

/* pi-tfx-ttSetup-4.i / / Definition of a static temp-table. */ DEFINE TEMP-TABLE ttCust FIELD CustNum LIKE Customer.CustNum FIELD Country LIKE Customer.Country FIELD Name LIKE Customer.Name FIELD Address LIKE Customer.Address FIELD Address2 LIKE Customer.Address2 FIELD City LIKE Customer.City FIELD State LIKE Customer.State FIELD PostalCode LIKE Customer.PostalCode FIELD Contact LIKE Customer.Contact FIELD Phone LIKE Customer.Phone FIELD SalesRep LIKE Customer.SalesRep FIELD CreditLimit LIKE Customer.CreditLimit FIELD Balance LIKE Customer.Balance FIELD Terms LIKE Customer.Terms FIELD Discount LIKE Customer.Discount FIELD Comments LIKE Customer.Comments FIELD Fax LIKE Customer.Fax FIELD EmailAddress LIKE Customer.EmailAddress INDEX CountryPost Country PostalCode INDEX Comments IS WORD-INDEX Comments INDEX CustNum IS UNIQUE PRIMARY CustNum INDEX NAME NAME INDEX SalesRep SalesRep.

/* pi-tfx-ttSetup-4.i */ 
/* Definition of a static temp-table. */ 
DEFINE TEMP-TABLE ttCust  
    FIELD CustNum      LIKE Customer.CustNum 
    FIELD Country      LIKE Customer.Country 
    FIELD Name         LIKE Customer.Name 
    FIELD Address      LIKE Customer.Address 
    FIELD Address2     LIKE Customer.Address2 
    FIELD City         LIKE Customer.City 
    FIELD State        LIKE Customer.State 
    FIELD PostalCode   LIKE Customer.PostalCode 
    FIELD Contact      LIKE Customer.Contact 
    FIELD Phone        LIKE Customer.Phone 
    FIELD SalesRep     LIKE Customer.SalesRep 
    FIELD CreditLimit  LIKE Customer.CreditLimit 
    FIELD Balance      LIKE Customer.Balance 
    FIELD Terms        LIKE Customer.Terms 
    FIELD Discount     LIKE Customer.Discount 
    FIELD Comments     LIKE Customer.Comments 
    FIELD Fax          LIKE Customer.Fax 
    FIELD EmailAddress LIKE Customer.EmailAddress 
   
    INDEX CountryPost Country PostalCode  
    INDEX Comments IS WORD-INDEX Comments 
    INDEX CustNum IS UNIQUE PRIMARY CustNum 
    INDEX NAME NAME 
    INDEX SalesRep SalesRep.

Here is the code sample:

/* pi-tfx-read-4.p / / Populates an empty static temp-table with records from an XML file. */ {pi-tfx-parameterVarDefs.i} {pi-tfx-ttSetup-4.i} DEFINE VARIABLE returnValue AS LOGICAL NO-UNDO. ASSIGN cSourceType = "FILE" cFile = "ttCust.xml" cReadMode = `"EMPTY"` cSchemaLocation = ? lOverrideDefaultMapping = ? cFieldTypeMapping = ? cVerifySchemaMode = ?. returnValue = `TEMP-TABLE ttCust:READ-XML`(cSourceType, cFile, cReadMode, cSchemaLocation, lOverrideDefaultMapping, cFieldTypeMapping, cVerifySchemaMode). IF returnValue THEN FOR EACH ttCust: DISPLAY CustNum Name FORMAT "X(30)". END.

/* pi-tfx-read-4.p */ 
/* Populates an empty static temp-table with records from an XML file. */ 
{pi-tfx-parameterVarDefs.i} 
{pi-tfx-ttSetup-4.i} 
DEFINE VARIABLE returnValue AS LOGICAL NO-UNDO. 
ASSIGN 
    cSourceType = "FILE" 
    cFile = "ttCust.xml"  
    cReadMode = "EMPTY" 
    cSchemaLocation = ? 
    lOverrideDefaultMapping = ? 
    cFieldTypeMapping = ? 
    cVerifySchemaMode = ?.  
  
returnValue = TEMP-TABLE ttCust:READ-XML(cSourceType, cFile, cReadMode,  
                                         cSchemaLocation,  
                                         lOverrideDefaultMapping,  
                                         cFieldTypeMapping, cVerifySchemaMode). 
IF returnValue THEN 
    FOR EACH ttCust: 
        DISPLAY CustNum Name FORMAT "X(30)". 
    END.

The code specifies the XML source file and displays a list of customer numbers and names after the read completes. Note the required TEMP-TABLE keyword in the method call.